package com.hnkywl.sts.dao.report;

import net.ruixin.orm.hibernate.SimpleHibernateTemplate;
import oracle.jdbc.OracleCallableStatement;
import org.apache.tools.ant.taskdefs.Java;
import org.hibernate.Query;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Repository;

import java.sql.*;
import java.sql.Date;
import java.util.*;

/**
 * Created with IntelliJ IDEA.
 * User: Administrator
 * Date: 13-6-14
 * Time: 下午4:51
 * To change this template use File | Settings | File Templates.
 */
@Repository
public class ZcfzbDao extends SimpleHibernateTemplate {

    /**
     * 财务收发存报表
     */
    public Map<String, List> zcfzbList(Long gsId, java.util.Date startDate, java.util.Date endDate,Integer pageNo, Integer pageSize) {
        if (gsId == null)
            gsId = -1L;
        Connection con = null;
        CallableStatement call = null;
        ResultSet rs = null;
        Calendar start = GregorianCalendar.getInstance();
        start.setTime(startDate);
        java.sql.Date paramStartDate = new java.sql.Date(start.getTime().getTime());
        Calendar end = GregorianCalendar.getInstance();
        end.setTime(endDate);
        java.sql.Date paramEndDate = new java.sql.Date(end.getTime().getTime());
        try {
            con = getSession().connection();
            call = con.prepareCall("{Call RPT_CW_ZCFZB(?,?,?,?)}");
            call.setLong(1, gsId);
            call.setDate(2, paramStartDate);
            call.setDate(3, paramEndDate);
            call.registerOutParameter(4, oracle.jdbc.OracleTypes.CURSOR);
            call.execute();
            //====================================
            rs = ((OracleCallableStatement) call).getCursor(4);
            ResultSetMetaData resultsMetaData = rs.getMetaData();
            int columnCount = resultsMetaData.getColumnCount();
            Map<String, List> result = new HashMap<String, List>();
            List<Map> rows = new ArrayList<Map>();
            while (rs.next()) {
                Map<String, Object> r = new HashMap<String, Object>();
                for (int i = 1; i <= columnCount; i++) {
                    String columnName = resultsMetaData.getColumnName(i).toLowerCase();
                    r.put(columnName, rs.getObject(i));
                }
                rows.add(r);
            }
            result.put("rows", rows);
            return result;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e.getMessage());
        } finally {
            closeResource(rs, call, con);
        }
    }

    public Map findzcfzbmxList(Long type,Long gsId, java.util.Date dateStart, java.util.Date dateEnd, int pageNo, int pageSize) {
        String sql = "select type as \"type\",flag as \"flag\",kmmc as \"kmmc\", " +
                "       dh as \"dh\",sbgs_id as \"sbgs_id\",sbgs_mc as \"sbgs_mc\", lx as \"lx\", " +
                "       kh_id as \"kh_id\", khmc as \"khmc\", jf as \"jf\", df as \"df\",drrq as \"drrq\", " +
                "       sbr_id as \"sbr_id\",sbrmc as \"sbrmc\",cjrq as \"cjrq\" " +
                "       from t_biz_temp_zcfz  where (flag =0 or flag = 1) and type = :type ";
        String countSql = "select count(1) from t_biz_temp_zcfz where (flag =0 or flag = 1) and type = :type ";
        String sumSql = "select type as \"type\",flag as \"flag\",kmmc as \"kmmc\", " +
                "       dh as \"dh\",sbgs_id as \"sbgs_id\",sbgs_mc as \"sbgs_mc\", lx as \"lx\", " +
                "       kh_id as \"kh_id\", khmc as \"khmc\", jf as \"jf\", df as \"df\",drrq as \"drrq\", " +
                "       sbr_id as \"sbr_id\",sbrmc as \"sbrmc\",cjrq as \"cjrq\" " +
                "       from t_biz_temp_zcfz  where flag=2 and type = :type ";
        Query sqlQuery = getSession().createSQLQuery(sql);
        Query countQuery = getSession().createSQLQuery(countSql);
        Query sumQuery = getSession().createSQLQuery(sumSql);
        sqlQuery.setLong("type", type);
        countQuery.setLong("type", type);
        sumQuery.setLong("type", type);
        sqlQuery.setFirstResult((pageNo - 1) * pageSize);
        sqlQuery.setMaxResults(pageSize);
        sqlQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        sumQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        Map result = new HashMap();
        result.put("total", countQuery.uniqueResult());
        result.put("rows", sqlQuery.list());
        result.put("footer", sumQuery.list());
        return result;
    }


    private void closeResource(ResultSet rs, Statement s, Connection con) {
        if (rs != null)
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
                throw new RuntimeException(e.getMessage());
            }
        if (s != null)
            try {
                s.close();
            } catch (SQLException e) {
                e.printStackTrace();
                throw new RuntimeException(e.getMessage());
            }
        if (con != null)
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
                throw new RuntimeException(e.getMessage());
            }
    }


    public String cbjz(Long gsId, java.util.Date startDate, java.util.Date endDate) {
        if (gsId == null)
            gsId = -1L;
        Connection con = null;
        CallableStatement call = null;
        ResultSet rs = null;
        Calendar start = GregorianCalendar.getInstance();
        start.setTime(startDate);
        java.sql.Date paramStartDate = new java.sql.Date(start.getTime().getTime());
        Calendar end = GregorianCalendar.getInstance();
        end.setTime(endDate);
        java.sql.Date paramEndDate = new java.sql.Date(end.getTime().getTime());
        try {
            con = getSession().connection();
            call = con.prepareCall("{Call RPT_CW_ZCFZB_RJ(?,?,?,?)}");
            call.setLong(1, gsId);
            call.setDate(2, paramStartDate);
            call.setDate(3, paramEndDate);
            call.registerOutParameter(4, Types.VARCHAR);
            call.execute();
            //====================================
            //====================================
            String errorMsg = call.getString(4);
            return errorMsg;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e.getMessage());
        } finally {
            closeResource(rs, call, con);
        }
    }


    public boolean checkZzcfzbJz (java.util.Date jzrq,Long gsId){
       String sql = "select count(1) from " +
               "t_biz_zjfz_rjb " +
               "where to_char(rjrq+1,'yyyyMMdd') = to_char(:rjrq,'yyyyMMdd') and sbgs_id = :sbgs_id ";
        Query countQuery = getSession().createSQLQuery(sql);
        countQuery.setDate("rjrq", jzrq);
        countQuery.setLong("sbgs_id",gsId);
        Integer sl = Integer.parseInt(countQuery.uniqueResult().toString());
        if(sl>0){
            return false;
        }else{
            return true;
        }
    }



}
